<?php

require_once 'VO/VOPlace.php';
require_once 'VO/VOTrip.php';
require_once 'VO/VOUser.php';
require_once 'VO/VOComment.php';
require_once 'VO/VOQuestion.php';
require("config.inc.php"); 

		
$test = new DatabaseWrapper();

/*$comment = new VOComment();
$comment->id = -1;
$comment->description = "cool comment2";
$comment->place_id = 28;
$comment->user_id =1;*/

$place_o2 = new VOPlace();
$place_o2->place_id = 28;
$place_o2->name = "NewPlace-plis";
$place_o2->lat = 1;
$place_o2->lon = 2;
$place_o2->state = 0;
$place_o2->user_description = "";
//$place_o2->comments_list = array($comment);
		
$places_list= array($place_o2);
				
$Trip1 = new VOTrip();
$Trip1->trip_id = 106;
$Trip1->name = 'Trip3';
$Trip1->state = 0;
$Trip1->description = '>S';
$Trip1->user_id = 1;
$Trip1->places_list = $places_list;

//$user = new VOUser();
//$user->address = "address";
//$user->id = 43;
//$user->age = -1;
//$user->mail = "basura@gmail.com";
//$user->lastname = "lastname";
//$user->name = "name3-updated";
//$user->photo = " ";
//$user->address = " ";
//$user->username = "x";
//$user->password = "x";
//$test->register($user);
//$result= $test->login("-","a");
//$result= $test->delete_Trip($Trip1);
//$result= $test->register($user);
$result= $test->save_Trip($Trip1);
//$result=$test->fetchAll_Trip(1);
//$result= $test->save_Comment($comment);
//$result= $test->search_users_byNameLastName('e');
//$result= $test->fetch_Comments($place_o2);
//echo 'test';


class DatabaseWrapper 
{
	

	public $link_id;
	
	/**********************************************
	 *   Constructor
	 *   	
	 *   Connecting to the databse 
	 */
 	public function __construct()
 	{
 	 	//connect to the database.
		//we could have used an abstracting layer for connecting to the database.
		//for the sake of simplicity, I choose not to.
		$this->link_id = mysql_connect(DB_SERVER, DB_USER, DB_PASS);
		mysql_select_db(DB_DATABASE);
 	 	
 	}	
	public function create_newType($place_object)
	{
		
	return($place_object);
	
	}
	
	/************************ COMMENTS FUNCTIONS******************************/
	public function fetch_Comments($current_place)
	{
		//echo $current_place->place_id;
		$ret_array_comments = array();
		$query_getComments= "SELECT * FROM comment c where c.place_id=".$current_place->place_id;
		$result_getComments = mysql_query($query_getComments,$this->link_id);
			
	    while ($row = mysql_fetch_object($result_getComments, "VOComment")) 
	    {
			$ret_array_comments[] = $row;
		}
			
		$ret_array_comments= $this->trip_addUserInformation($ret_array_comments);    
		mysql_free_result($result_getComments);
		return($ret_array_comments);
		
	}
		
	public function save_Comment($comment_obj)
	{
		// in flex you have to create a Comment and assign in comment->id, curret_user id and 
		// comment-->place_id;
		
		date_default_timezone_set("Europe/Paris");		
		$current_date = date("Y-m-d");
		
		//if($comment_obj->id==-1)
		//{
			
			
			$sql_insert_comment = "INSERT INTO comment (description, user_id, post_date,place_id) VALUES ('".$comment_obj->description.
			"',".$comment_obj->user_id.",'".
			$current_date."',".
			$comment_obj->place_id.")";
					
			$result_insert_comment = @mysql_query($sql_insert_comment,$this->link_id); 
			$new_comment_id = mysql_insert_id();
			
			$comment_obj->id = $new_comment_id;
			
		//}
		/*else//update, if there was a problem send in comment_obj description the error message
		{
			$sql_update_comment = "UPDATE comment set description='".$comment_obj->description.				
				"',post_date='".$comment_obj->post_date.
				"' where id= ".$comment_obj->id;
				
				$result_update_comment = @mysql_query($sql_update_comment,$this->link_id);
				if(!$result_update_comment)
					$comment_obj->description ='update_error';
			
		}*/
		
		return($comment_obj);
		
	}
	public function delete_Comment($comment_obj)
	{
		$query_delete_comment = "DELETE  FROM comment where id= ".$comment_obj->id;
		$result_delete_comment = @mysql_query($query_delete_comment,$this->link_id); 
		$affected_rows = @mysql_affected_rows($this->link_id);
		if($affected_rows!=0)
			return(true);
		else
			return(false);
		 	
		
	}
	
	/********************************** TRIP FUNCTIONS ******************************/
	public function fetchAll_Trip($user_id) 
	{
		
		
		$query = "SELECT * FROM trip as t where t.user_id=".$user_id;
		$result = mysql_query($query,$this->link_id);		
		$ret = array();
		
	    while ($row = mysql_fetch_object($result, "VOTrip")) 
	    {
			$ret[] = $row;
		}
		
		foreach($ret as $current_trip)
		{
			 $new_trip_id=$current_trip->trip_id;
			 
			  $query = "SELECT p.name,p.lat,p.lon, p.place_id, p.description, p.state FROM place as p where p.trip_id= ".$current_trip->trip_id;			 
        	  $place_list = mysql_query($query);

        	  /************** places list *********************/
        	  $places = array();
			  while ($row = mysql_fetch_object($place_list, "VOPlace")) 
			  {
					$places[] = $row;
					//echo $row->place_id;
			  }
			  $current_trip-> places_list = $places;
			  /************** comments list *********************/
			  foreach($current_trip->places_list as $current_place)
			  {			  	
			  	 $current_place->comments_list = $this->fetch_Comments($current_place);
			  	 //echo 'test';
			  }
			  
			  
			  
		}
		
		// free the memory used by the mysql handler
		mysql_free_result($result);
		// return the result
		return $ret;
	}
			
	public function search_users_byNameLastName($query)
	{		
		$trip_main_array= array();
		//$query_userinfo_search = "SELECT name,lastname  FROM user where user_id =".$query;
		$query_search = "SELECT id  FROM user where name like '%".$query. "%' or lastname like  '%" .$query."%'";		
		$result_search = @mysql_query($query_search,$this->link_id);//user_ids that match previous query 	
		while ($row = mysql_fetch_array($result_search, MYSQL_ASSOC)) 
		{    		
    		$trips_by_id = $this->fetchAll_Trip($row["id"]);//BY USER_ID
     		$trips_by_id = $this->trip_addUserInformation($trips_by_id);    		
    		$trip_main_array = array_merge($trip_main_array, $trips_by_id);    		    	    
		}
		return($trip_main_array);
	}
	private function trip_addUserInformation($trips_by_id)
	{
		foreach($trips_by_id as $trip)
		{
			//echo $trip->user_id;
			$query_get_userinfo = "SELECT * FROM user as u where u.id=".$trip->user_id;
			$result_search = @mysql_query($query_get_userinfo,$this->link_id);//user_ids that match previous query
			
			$user_object =mysql_fetch_object($result_search, "VOUser");
			$trip->user_name_lastname = $user_object->name." ".$user_object->lastname ;
			
			
		}
		//mysql_free_result($result_search);
		return($trips_by_id);
	}
	
	public function save_Trip($trip_object)
	{
		
	
		if($trip_object->trip_id==-1)//insert a empty trip(add new empty trip- tree(addTrip))
		{			
			
			$sql = "INSERT INTO trip (name, user_id, state) VALUES ('".$trip_object->name.
			"','".$trip_object->user_id."','".			
			$trip_object->state."')";
			
			
			$query_id = @mysql_query($sql,$this->link_id); 
			$new_trip_id = mysql_insert_id();
			$trip_object->trip_id = $new_trip_id;
					
		}
		else // ifTripExists (edit trip information or save/update new places in a trip)
		{
			//edit trip information						
			//$sql_trip_update = "UPDATE trip set name='".$trip_object->name."', state=".$trip_object->state." where trip_id=".$trip_object->trip_id;
			$sql_trip_update = "UPDATE trip set name='".$trip_object->name."', state=".$trip_object->state.", description='".$trip_object->description."' where trip_id=".$trip_object->trip_id;			
			
			$query_result= @mysql_query($sql_trip_update,$this->link_id);
			
			/******add places or edit places in a trip*******/
			foreach($trip_object->places_list as $place)
			{

				  /******if that place dont exist in DB, insert******(lat, lon, name,descrip,state, place_id)*/
					if($place->place_id==-1)
					{	
										
						$sql_place_i = "INSERT INTO place (name, lat, trip_id,lon) VALUES ('".$place->name.
						"',".mysql_real_escape_string($place->lat).",".$trip_object->trip_id.",".
						mysql_real_escape_string($place->lon).")";
						$query_id = @mysql_query($sql_place_i,$this->link_id); 
						$new_trip_id = mysql_insert_id();
						$place->place_id = $new_trip_id;
						
					}
					else //update place information (name, lat, long, state,descript)
					{
					
						
						$sql_place_up = "UPDATE place set name='".$place->name."', lat=".mysql_real_escape_string($place->lat).",lon=".mysql_real_escape_string($place->lon).",state=".$place->state.",description='".$place->user_description."' where place_id= ".$place->place_id;
						$result = @mysql_query($sql_place_up,$this->link_id);
						

					}
			}
				
			
			 
			
		}
					
		
		return($trip_object);
		
	
	}
	
	
	public function delete_Trip($trip_object)
	{
			$affected_rows_trip =-1;
																
			 mysql_query('SET AUTOCOMMIT=0',$this->link_id);	 
			 							
				//first delete comments if they exists!
				if($trip_object->places_list!=null)
				{
					foreach($trip_object->places_list as $place)
					{
				 		$query_delete_comments = "DELETE  FROM comment where place_id= ".$place->place_id;
				 		$query_comments_r = @mysql_query($query_delete_comments,$this->link_id);
					}
				}
			 	$query_delete_places = "DELETE  FROM place where trip_id= ".$trip_object->trip_id;
			 	$query_places_r = @mysql_query($query_delete_places,$this->link_id); 
			 	
			 	 						 							 										
			 if($query_places_r)
			 {			
			 			 	
			 	$query_delete_trip = "DELETE  FROM trip where trip_id= ".$trip_object->trip_id;							 							 
				$query_trip_r = mysql_query($query_delete_trip);															
				$affected_rows_trip = mysql_affected_rows($this->link_id); 								
			 }							
			if(mysql_errno()==0)
			{ 
				
				mysql_query('COMMIT');
				if($affected_rows_trip!=0)
					return($query_trip_r);
				else
					return(false);
				
			}
			else 
			{
					
				mysql_query('ROLLBACK');
				return(false);
				
			}
							
	}	
	
	
	public function deletePlace($current_place)
	{
		//delete place will delete all comments related to that place..
		
		mysql_query('SET AUTOCOMMIT=0',$this->link_id);	 
		
		$query_delete_comments = "DELETE  FROM comment where place_id= ".$current_place->place_id;		
		$result_delete_comments = @mysql_query($query_delete_comments ,$this->link_id); 
							
		if($result_delete_comments)
		{			
							 			 	
				$query_delete_place = "DELETE  FROM place where place_id= ".$current_place->place_id;
				$result_delete_place = @mysql_query($query_delete_place,$this->link_id); 
				$affected_rows = @mysql_affected_rows($this->link_id); 	
																
		}
		//if no errors we can commit 
		if(mysql_errno()==0)
		{ 
				//echo 'submit';
				mysql_query('COMMIT');
				if($affected_rows!=0)
						return($result_delete_place);
				else
						return(false);
		}
		else 
		{
								//echo 'roolbcak';	
				mysql_query('ROLLBACK');
				return($result_delete_place);
								
		}
		
		
		
		
		
	}
	
	
	/************************ user managment ******************************/
	/**
	 * Login service with the pasic parameters
	 * @param unknown_type $username
	 * @param unknown_type $password
	 */
	public function login($username, $password)
	{
		$user = new VOUser(); 
		
		
		//VALIDATE USERNAME exists in db!
		$query_user_exists = "SELECT * FROM user as currentUser 
				  where currentUser.username='".$username."'";
		$result_user_exists = mysql_query($query_user_exists,$this->link_id);		
		$user_exists = @mysql_affected_rows($this->link_id);  
				
		//VALIDATE USERNAME_MATCH PASSWORD?
		if($user_exists)
		{
			$query_match_pass = "SELECT * FROM user as currentUser 
			where currentUser.username='".$username ."' and currentUser.password='".$password."'";
			$result_match_pass = mysql_query($query_match_pass);
			$pass_match = @mysql_affected_rows($this->link_id);
			if($pass_match!=0)
				$user = mysql_fetch_object($result_match_pass, "VOUser");
			else
			{				
				$user->username = $username;
				$user->password = INVALID_PASSWORD;
			}
				
		}
		else
		 $user->username = INVALID_USERNAME; 
					
		
		// return the user
		return $user;
	}
	
	public function register ($user)
	{
		//$result: true(query ok)=1, false(internal error), false(user already exists in db)
		
		$query_user_exists = "SELECT * FROM user as currentUser 
				  where currentUser.username='".$user->username."'";
		$result = mysql_query($query_user_exists);		
		$user_exists = @mysql_affected_rows($this->link_id);  
		$variable = mysql_fetch_object($result,"VOUser");
		
		if($user_exists==0)//if theres no user in the db create new one
		{
			/*$query = "INSERT INTO user VALUES ('".$user->id."','"
	                                                   .$user->name."','"
	                                                   .$user->lastname."','"
	                                                   .$user->username."','"
	                                                   .$user->password."','"
	                                                   .$user->mail."','"
	                                                   .$user->address."',"
	                                                   .$user->age.",'"
	                                                   .$user->photo."')"  ;
	         $result = mysql_query($query);*/
			//if($user->id== -1)
			//{
				$sql_insert_new_user = "INSERT INTO user (name, lastname, username, password,mail) VALUES ('".$user->name.
				"','".$user->lastname."','".$user->username."','".$user->password."','"
				.$user->mail."')";				
				$result = @mysql_query($sql_insert_new_user,$this->link_id);											
			//}					                                                  	         
		}
		else 
		   $result=false;
		 
        return $result;
			
		  		
	}
	
	
	public function update_profile($user)
	{		
		//username cannot be updated, just once created!!!
		// we have to pass always default values for: lastname, password, mail, address, photo, age
		// returns true: updated correct, false update incorrect.		
		if($user->id!=-1)
		{
				$sql_profile_up = "UPDATE user set name='".$user->name.
				"', lastname='".$user->lastname.
				"',password='".$user->password.
				"',mail='".$user->mail.
				"',address='".$user->address.
				"',age=".$user->age.
				",photo='".$user->photo.
				"' where id= ".$user->id;
				
				$result_update = @mysql_query($sql_profile_up,$this->link_id);
				if($result_update)
				{
					return(true);
				}
				else
				   return(false);
				
					
		}
		return(false);
				
		
		
	}
	
	

	
	
	
	
	
	
}

?>